﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class WelfareLoanPaymentBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void insert(int cont_seq,string pay_date, string remarks, int period, int fiscal_month
            , int fiscal_year, int pay_flag, double  pay_amount, double  pay_interest, int created_by)
        {
            try
            {

                DateTime dt;
                dt = Convert.ToDateTime(pay_date);  


                dbManager.Open();
                dbManager.CreateParameters(12);
                dbManager.AddParameters(0, "@pay_date", dt);
                dbManager.AddParameters(1, "@remarks", remarks);
                dbManager.AddParameters(2, "@period", period);
                dbManager.AddParameters(3, "@fiscal_month", fiscal_month);
                dbManager.AddParameters(4, "@fiscal_year", fiscal_year);
                dbManager.AddParameters(5, "@pay_flag", pay_flag);
                dbManager.AddParameters(6, "@pay_amount", pay_amount);
                dbManager.AddParameters(7, "@pay_interest", pay_interest);
                dbManager.AddParameters(8, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(9, "@created_by", created_by);
                dbManager.AddParameters(10,"@created_when", DateTime.Today);
                dbManager.AddParameters(11,"@cont_seq",cont_seq );
           
  
                string sql;



                sql = " insert into hrm_wf_contract_dtls"
                + "(cont_seq,"
                + "period,"
                + "pay_date,"
                + "pay_amount,"
                + "pay_interest,"
                + " remarks,"
                + "record_status,"
                + "created_by,"
                + "created_when,"
                + "fiscal_month,"
                + "fiscal_year,"
                + "pay_flag) "
                
                + " values"
                + "(@cont_seq,"
                + "@period,"
                + "@pay_date,"
                + "@pay_amount,"
                + "@pay_interest,"
                + " @remarks,"
                + "@record_status,"
                + "@created_by,"
                + "@created_when,"
                + "@fiscal_month,"
                + "@fiscal_year,"
                + "@pay_flag)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }



        public DataTable Select(int cont_seq) //radio fix
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "SELECT *,iif(pay_flag=1,'ชำระเอง','หักผ่านบัญชีธนาคาร') as pay_flag2,"
                    + " hrm_wf_contracts.amt_period,"
                    + " cast(concat(hrm_wf_contract_dtls.period,'/',hrm_wf_contracts.amt_period) as char) AS period2"
                    + " from hrm_wf_contract_dtls"
                    + " Inner Join hrm_wf_contracts ON hrm_wf_contracts.cont_seq = hrm_wf_contract_dtls.cont_seq"
                    + " where hrm_wf_contract_dtls.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
                    + " and hrm_wf_contract_dtls.cont_seq=" + cont_seq ;

                    

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }





        public DataTable SelectExtByID(int ext_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT ext_seq,ext_name"
+ " from hrm_pr_extra_types"
+ " where ext_seq='" + ext_seq + "'"
+ " and record_status = '" + SystemInfo._ActiveRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }




        public DataTable SelectExt(string ext_flag)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT ext_seq,ext_name,ext_flag"
+ " from hrm_pr_extra_types"
+ " where ext_flag='" + ext_flag + "'"
+ " and record_status = '" + SystemInfo._ActiveRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }






        public void Update(int cont_dtls_seq, string pay_date, string remarks, int period, int fiscal_month
            , int fiscal_year, int pay_flag, double pay_amount, double pay_interest, int updated_by)
        {
            try
            {

                DateTime dt;
                dt = Convert.ToDateTime(pay_date);


                dbManager.Open();
                dbManager.CreateParameters(12);
                dbManager.AddParameters(0, "@cont_dtls_seq", cont_dtls_seq);
                dbManager.AddParameters(1, "@pay_date", dt);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@period", period);
                dbManager.AddParameters(4, "@fiscal_month", fiscal_month);
                dbManager.AddParameters(5, "@fiscal_year", fiscal_year);
                dbManager.AddParameters(6, "@pay_flag", pay_flag);
                dbManager.AddParameters(7, "@pay_amount", pay_amount);
                dbManager.AddParameters(8, "@pay_interest", pay_interest);
                dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@updated_by", updated_by);
                dbManager.AddParameters(11, "@updated_when", DateTime.Today);
               

                string sql;



                sql = " update hrm_wf_contract_dtls "
                + " set"
                + " pay_date=@pay_date,"
                + " remarks=@remarks,"
                + " period=@period,"
                + " fiscal_month=@fiscal_month,"
                + " fiscal_year=@fiscal_year,"
                + " pay_flag=@pay_flag,"
                + " pay_amount=@pay_amount,"
                + " pay_interest=@pay_interest,"
                + " record_status=@record_status,"
                + " updated_by=@updated_by,"
                + " updated_when=@updated_when"
                + " where cont_dtls_seq=" + cont_dtls_seq;
              

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

             }

        }




        public void Delete(int cont_dtls_seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@cont_dtls_seq", cont_dtls_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;

                sql = " update hrm_wf_contract_dtls "
              + " set"
              + " record_status=@record_status,"
              + " updated_by = @updated_by,"
              + " updated_when = @updated_when"
              + " where cont_dtls_seq = @cont_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
      
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public Boolean chkDupPeriod(int cont_seq, int period)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@cont_seq", cont_seq);
                dbManager.AddParameters(1, "@period", period);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);

                string sql;
                sql = "select * from hrm_wf_contract_dtls"

                    + " where period = @period"
                    + " and cont_seq = @cont_seq"
                    + " and record_status= @record_status";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        public Boolean chkDupPeriodEdit(int period, int cont_dtls_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Period", period);
                dbManager.AddParameters(1, "@cont_dtls_seq", cont_dtls_seq);
                dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                string sql;
                sql = "select cont_dtls_seq "
                        + " from hrm_wf_contract_dtls"
                    + " where period = @Period"
                        + " and cont_dtls_seq = @cont_dtls_seq"
                        + " and record_status = @ActiveRecord";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {

                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }






     

   
    }
}
